﻿#include "CMysqlFunc.h"

static ColArr BadCol{};
static FieldArr BadField{};

CMysqlFunc cMysql;
typedef std::string str;

CMysqlFunc::CMysqlFunc(const char * host, const char * user, const char * passwd, const char * db, unsigned int port)
	:res(nullptr)
{
	mysql_init(&Mysql);
	if (!(mysql_real_connect(&Mysql, host, user, passwd, db, port, NULL, 0)))
		printf("Error connecting to database:%s\n", mysql_error(&Mysql));
	mysql_query(&Mysql, "set names gbk");

	HeadList = new FieldList;
	TailList = HeadList;
}

CMysqlFunc::~CMysqlFunc()
{
	if (res != nullptr)
		mysql_free_result(res);
	mysql_close(&Mysql);
	for (FieldList* pList = TailList; pList;)
	{
		FieldList *Tmp_List = pList->LastField;
		if (pList != HeadList)
			DeleteFieldArr(*(pList->FieldValue));
		else
			delete pList;
		pList = Tmp_List;
	}
}

void CMysqlFunc::DeleteFieldArr(FieldArr Res)
{
	FieldList* Tmp_List = HeadList;
	FieldArr* DeleteArr{};
	while (Tmp_List = Tmp_List->NextField)
	{
		if (Tmp_List->FieldValue->HeadCol == Res.HeadCol)
		{
			DeleteArr = Tmp_List->FieldValue;
			if (Tmp_List != TailList)
			{
				Tmp_List->LastField->NextField = Tmp_List->NextField;
				Tmp_List->NextField->LastField = Tmp_List->LastField;
			}
			if (Tmp_List == TailList)
			{
				Tmp_List->LastField->NextField = nullptr;
				TailList = Tmp_List->LastField;
			}
			delete Tmp_List;
			break;
		}
	}

	FieldArr *tmpfield{}, *tmpfield2{};
	ColArr *tmpcol{}, *tmpcol2{};
	if (DeleteArr)
		for (tmpfield = DeleteArr; tmpfield;)
		{
			for (tmpcol = tmpfield->HeadCol; tmpcol;)
			{
				tmpcol2 = tmpcol->Next;
				delete tmpcol;
				tmpcol = tmpcol2;
			}
			tmpfield2 = tmpfield->NextCol;
			delete tmpfield;
			tmpfield = tmpfield2;
		}
}

FieldArr CMysqlFunc::Select(const char * TableName, const char**FieldName, int ColunmnNum, int * ReturnRows, int* ReturnCols, const char* Condition, const char * OrderByColumn, bool IsAsc, int LimitNum, int LimitBias)
//FieldArr* CMysqlFunc::Select(const char * TableName, str*FieldName, int ColunmnNum, int * ReturnRows, const char * OrderByColumn, bool IsAsc, int LimitNum, int LimitBias)
{
	if (this->res != nullptr)
	{
		mysql_free_result(res);
		res = nullptr;
	}
	str QureyWord = "SELECT ";
	for (int i = 0; i < ColunmnNum; i++)
		QureyWord = QureyWord + '`' + FieldName[i] + "` ,";
	QureyWord.pop_back();
	QureyWord = QureyWord + " FROM " + TableName;
	if (Condition != nullptr)
		QureyWord = QureyWord + " WHERE " + Condition + ' ';
	if (OrderByColumn != nullptr)
		QureyWord = QureyWord + " ORDER BY " + OrderByColumn + (IsAsc ? " ASC " : " DESC ");
	if (LimitNum > 0)
	{
		if (LimitBias > 0)
			QureyWord = QureyWord + " LIMIT " + std::to_string(LimitBias) + "," + std::to_string(LimitNum);
		else
			QureyWord = QureyWord + " LIMIT " + std::to_string(LimitNum);
	}

	return Select(QureyWord.c_str(), ReturnRows, ReturnCols);
}

FieldArr CMysqlFunc::Select(const char * selectname, int *ReturnRows, int * ReturnCols)
{
	int AffectCols{};
	int AffectRows{};
	if (mysql_query(&Mysql, selectname))
	{
		if (IfPrintErrorInfo)
			printf("Select Failed: %s\n", mysql_error(&Mysql));
		if (ReturnRows)
			*ReturnRows = 0;
		if (ReturnCols)
			*ReturnCols = 0;
		return BadField;
	}

	res = mysql_store_result(&Mysql);
	AffectCols = mysql_num_fields(res);
	AffectRows = mysql_num_rows(res);
	if (ReturnRows)
		*ReturnRows = AffectRows;
	if (ReturnCols)
		*ReturnCols = AffectCols;

	FieldArr* pStringRes = new FieldArr;
	pStringRes->HeadCol = new ColArr;
	FieldList* RecList = new FieldList;
	TailList->NextField = RecList;
	RecList->LastField = TailList;
	RecList->FieldValue = pStringRes;
	TailList = RecList;

	ColArr** ptrArr = new ColArr*[AffectCols];

	MYSQL_FIELD* tmp_field{};
	int PosClo = 0;
	while (tmp_field = mysql_fetch_field(res))
	{
		if (PosClo == 0)
		{
			pStringRes->ColName = str(tmp_field->name);
			ptrArr[PosClo] = pStringRes->HeadCol;
		}
		else
		{
			FieldArr* CurCol = &(*pStringRes + (PosClo - 1));
			if (CurCol->NextCol == nullptr)
			{
				FieldArr* tmpArr = new FieldArr;
				tmpArr->HeadCol = new ColArr;
				CurCol->NextCol = tmpArr;
			}
			CurCol->NextCol->ColName = str(tmp_field->name);
			ptrArr[PosClo] = CurCol->NextCol->HeadCol;
		}
		PosClo++;
	}

	MYSQL_ROW tmp_row{};
	bool RowFirstFlag = true;
	while (tmp_row = mysql_fetch_row(res))
	{
		if (RowFirstFlag)
		{
			for (int i = 0; i < AffectCols; i++)
				if (tmp_row[i])//有时数据为空
					ptrArr[i]->Value = str(tmp_row[i]);
			RowFirstFlag = false;
		}
		else
			for (int i = 0; i < AffectCols; i++)
			{
				if (ptrArr[i]->Next == nullptr)
					ptrArr[i]->Next = new ColArr;
				if (tmp_row[i])//有时数据为空
					ptrArr[i]->Next->Value = str(tmp_row[i]);
				ptrArr[i] = ptrArr[i]->Next;
			}
	}

	delete[] ptrArr;
	FieldArr StringRes = *pStringRes;
	return StringRes;
}

bool CMysqlFunc::Insert(const char * TableName, const char ** FieldName, const char ** ColumnValues, int ColumnNum, int RowNum, int* IsFormula)
{
	str QueryWord = "INSERT INTO " + str(TableName) + " (";
	for (int i = 0; i < ColumnNum; i++)
		QueryWord = QueryWord + "`" + FieldName[i] + "`,";
	QueryWord.pop_back();
	QueryWord = QueryWord + ") VALUES ";
	for (int j = 0; j < RowNum; j++)
	{
		QueryWord += '(';
		if (IsFormula != nullptr)
		{
			for (int i = 0; i < ColumnNum; i++)
				if (IsFormula[i])
					QueryWord = QueryWord + " " + ColumnValues[j*ColumnNum + i] + " ,";
				else
					QueryWord = QueryWord + "\'" + ColumnValues[j*ColumnNum + i] + "\',";
		}
		else
			for (int i = 0; i < ColumnNum; i++)
				QueryWord = QueryWord + "\'" + ColumnValues[j*ColumnNum + i] + "\',";
		QueryWord.pop_back();
		QueryWord += "),";
	}
	QueryWord.pop_back();

	return Query(QueryWord.c_str(), 1);
}

bool CMysqlFunc::Update(const char * TableName, const char ** FieldName, const char ** ColumnValues, int ColumnNum, const char * Condition, int* IsFormula)
{
	str QueryWord = "UPDATE " + str(TableName) + " SET ";
	if (IsFormula != nullptr)
	{
		for (int i = 0; i < ColumnNum; i++)
		{
			if (IsFormula[i])
				QueryWord = QueryWord + "`" + FieldName[i] + "`= " + ColumnValues[i] + " ,";
			else
				QueryWord = QueryWord + "`" + FieldName[i] + "`= '" + ColumnValues[i] + "' ,";
		}
	}
	else
		for (int i = 0; i < ColumnNum; i++)
			QueryWord = QueryWord + "`" + FieldName[i] + "`= '" + ColumnValues[i] + "' ,";
	QueryWord.pop_back();
	QueryWord = QueryWord + " WHERE " + Condition;

	return Query(QueryWord.c_str(), 2);
}

bool CMysqlFunc::Delete(const char * TableName, const char * Condition)
{
	str QueryWord = "DELETE FROM " + str(TableName) + " WHERE " + Condition;
	return Query(QueryWord.c_str(), 3);
}

bool CMysqlFunc::Query(const char * Queryname, int Mysqltype)
{
	if (mysql_query(&Mysql, Queryname))
	{
		if (IfPrintErrorInfo)
			switch (Mysqltype)
			{
			case 0:
				printf("Query failed: %s\n", mysql_error(&Mysql));
				break;
			case 1:
				printf("Insert failed: %s\n", mysql_error(&Mysql));
				break;
			case 2:
				printf("update failed: %s\n", mysql_error(&Mysql));
				break;
			case 3:
				printf("delete failed: %s\n", mysql_error(&Mysql));
				break;
			default:
				break;
			}
		return 0;
	}
	else
		return 1;
}

bool CMysqlFunc::CreateTable(const char * TableName, const char ** FieldName, const char ** LimitName, int FieldNum, const char * PrimaryKey, const char * Engine, const char * CharSet)
{
	str CreateWord = "CREATE TABLE IF NOT EXISTS " + str(TableName) + '(';
	for (int i = 0; i < FieldNum; i++)
		CreateWord = CreateWord + FieldName[i] + ' ' + LimitName[i] + " ,";
	CreateWord = CreateWord + " PRIMARY KEY(" + PrimaryKey + ")) ENGINE= " + Engine + " DEFAULT CHARSET= " + CharSet;
	if (mysql_query(&Mysql, CreateWord.c_str()))
	{
		if (IfPrintErrorInfo)
			printf("Create table failed: %s\n", mysql_error(&Mysql));
		return false;
	}
	return true;
}

bool CMysqlFunc::InsertField(const char * TableName, const char ** FieldName, int FieldNum, const char** FieldProperty, bool ChangeOrder)
{

	std::vector<str> VSQLName;
	std::set<str> SSQLName;
	int CountField;
	str tmp_str = "table_name=\'" + str(TableName) + '\'';
	bool SucFlag = true;
	int FieldBias = 0;

	const char** SFieldProperty = new const char*[FieldNum];
	if (FieldProperty)
		SFieldProperty = FieldProperty;
	else
		for (int i = 0; i < FieldNum; i++)
			SFieldProperty[i] = " CHAR(20) ";

	str PrintStr{};
	const char* SQLName[2] = { "COLUMN_NAME","ORDINAL_POSITION" };
	FieldArr tmp_Arr = Select("information_schema.COLUMNS", SQLName, 2, &CountField, nullptr, tmp_str.c_str());
	for (int i = 0; i < CountField; i++)
	{
		SSQLName.insert(tmp_Arr[0][i]);
		if (ChangeOrder)
			for (int j = 0; j < CountField; j++)
			{
				if (atoi(tmp_Arr[1][j].c_str()) == i + 1)
				{
					VSQLName.push_back(tmp_Arr[0][j]);
					break;
				}
			}
	}
	cMysql.DeleteFieldArr(tmp_Arr);
	for (int i = 0; i < FieldNum; i++)
	{
		str tmp_str;
		if (ChangeOrder)
		{
			str PosStr{};
			if (i == 0)
				PosStr = " FIRST ";
			else
				PosStr = " AFTER " + str(FieldName[i - 1]);
			if (SSQLName.find(str(FieldName[i])) == SSQLName.end())
			{
				tmp_str = "ALTER TABLE " + str(TableName) + " ADD COLUMN " + FieldName[i] + ' ' + SFieldProperty[i] + PosStr;
				PrintStr = str(TableName) + "：增加" + FieldName[i] + "，属性" + SFieldProperty[i] + " 在第" + std::to_string(i + 1) + "列。";
			}
			else
			{
				tmp_str = "ALTER TABLE " + str(TableName) + " MODIFY " + FieldName[i] + ' ' + SFieldProperty[i] + PosStr;
				PrintStr = str(TableName) + "：移动" + FieldName[i] + "，属性" + SFieldProperty[i] + " 到第" + std::to_string(i + 1) + "列。";
			}
		}
		else
		{
			if (SSQLName.find(str(FieldName[i])) != SSQLName.end())
				continue;
			else
			{
				tmp_str = "ALTER TABLE " + str(TableName) + " ADD COLUMN " + FieldName[i] + ' ' + SFieldProperty[i];
				PrintStr = str(TableName) + "：增加" + FieldName[i] + "，属性" + SFieldProperty[i] + " 在最后一列。";
			}
		}
		if (mysql_query(&Mysql, tmp_str.c_str()))
		{
			printf("Insert field into table failed:%s\n", mysql_error(&Mysql));
			SucFlag = false;
		}
		else
		{
			printf(PrintStr.c_str());
			printf("\n");
		}
	}
	if (ChangeOrder)
	{
		VSQLName.clear();
		tmp_Arr = Select("information_schema.COLUMNS", SQLName, 2, &CountField, nullptr, tmp_str.c_str());
		for (int i = FieldNum; i < CountField; i++)
		{
			for (int j = 0; j < CountField; j++)
			{
				if (atoi(tmp_Arr[1][j].c_str()) == i + 1)
				{
					VSQLName.push_back(tmp_Arr[0][j]);
					break;
				}
			}
		}
		cMysql.DeleteFieldArr(tmp_Arr);
		str tmp_del_str = "ALTER TABLE " + str(TableName);
		str tmp_del_rec{ " " };
		for (auto it : VSQLName)
		{
			tmp_del_str = tmp_del_str + " DROP COLUMN `" + it + "`,";
			tmp_del_rec = tmp_del_rec + it + ",";
		}
		tmp_del_rec.pop_back();
		tmp_del_str.pop_back();
		if (mysql_query(&Mysql, tmp_del_str.c_str()))
		{
			SucFlag = false;
			printf("delete columns failed:%s\n", mysql_error(&Mysql));
		}
		else
		{
			if (VSQLName.size())
			{
				PrintStr = str(TableName) + "：删除" + tmp_del_rec + "列";
				printf(PrintStr.c_str());
				printf("\n");
			}
		}
	}
	return SucFlag;
}

bool CMysqlFunc::DescribleTable(const char * TableName)
{
	str tmp_str = "DESC " + str(TableName);
	int ReturnCols{}, ReturnRows{};
	FieldArr arr = cMysql.Select(tmp_str.c_str(), &ReturnRows, &ReturnCols);
	for (int i = 0; i < (ReturnCols); i++)
		std::cout << (arr + i).ColName << '\t';
	std::cout << '\n';
	for (int j = 0; j < (ReturnRows); j++)
	{
		for (int i = 0; i < (ReturnCols); i++)
			std::cout << arr[i][j] << '\t';
		std::cout << '\n';
	}
	cMysql.DeleteFieldArr(arr);
	return false;
}

bool CMysqlFunc::ModifyField(const char * TableName, const char * FieldName, const char * FieldProperty)
{
	str tmp_str = "ALTER TABLE " + str{ TableName }+" MODIFY " + str{ FieldName }+' ' + str{ FieldProperty };
	return mysql_query(&Mysql, tmp_str.c_str());
}

FieldArr::~FieldArr()
{
	ColName = "";
	NextCol = nullptr;
	HeadCol = nullptr;
}

FieldArr &FieldArr::operator+(int i)
{
	FieldArr* res = this;
	while (i-- > 0)
		res = res->NextCol;
	if (res != nullptr)
		return *res;
	else
		return BadField;
}

ColArr &FieldArr::operator[](int i)
{
	FieldArr* res = this;
	while (i-- > 0)
		res = res->NextCol;
	if (res != nullptr)
		return *(res->HeadCol);
	else
		return BadCol;
}

ColArr &FieldArr::operator[](char * str)
{
	FieldArr* res = this;
	do
	{
		if (strcmp(str, ColName.c_str()))
			res = res->NextCol;
		else
			return *(res->HeadCol);
	} while (res->NextCol != nullptr);
	return BadCol;
}

ColArr::~ColArr()
{
	Value = "";
	Next = nullptr;
}

str &ColArr::operator[](int i)
{
	ColArr* res = this;
	while (i-- > 0)
		res = res->Next;
	if (res != nullptr)
		return res->Value;
	else
	{
		str str{};
		return str;
	}
}

ColArr &ColArr::operator+(int i)
{
	ColArr* res = this;
	while (i-- > 0)
		res = res->Next;
	if (res != nullptr)
		return *res;
	else
		return BadCol;
}